import streamlit as st
import pandas as pd
import plotly.express as px
import mysql.connector
from datetime import datetime, timedelta
import hashlib
import random
# 数据库配置
DB_CONFIG = {
    'host': '',
    'user': '',
    'password': '',
    'database': ''
}


# 初始化数据库连接
def get_db_connection():
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        return conn
    except mysql.connector.Error as err:
        st.error(f"数据库连接错误: {err}")
        return None


# 创建必要的表（如果不存在）
def initialize_database():
    conn = get_db_connection()
    if conn:
        cursor = conn.cursor()

        # 创建用户表
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(50) UNIQUE NOT NULL,
            password_hash VARCHAR(255) NOT NULL,
            is_admin BOOLEAN DEFAULT FALSE
        )
        """)

        # 创建健康评分表
        # cursor.execute("drop table health_scores")
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS health_scores (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            score DOUBLE NOT NULL,
            date DATE NOT NULL,
            FOREIGN KEY (user_id) REFERENCES users(id)
        )
        """)

        # 插入示例数据（仅用于演示）
        try:
            # 示例用户
            cursor.execute("INSERT IGNORE INTO users (username, password_hash, is_admin) VALUES (%s, %s, %s)",
                           ('user1', hashlib.sha256('user1'.encode()).hexdigest(), False))
            cursor.execute("INSERT IGNORE INTO users (username, password_hash, is_admin) VALUES (%s, %s, %s)",
                           ('user2', hashlib.sha256('user2'.encode()).hexdigest(), False))
            cursor.execute("INSERT IGNORE INTO users (username, password_hash, is_admin) VALUES (%s, %s, %s)",
                           ('admin', hashlib.sha256('admin'.encode()).hexdigest(), True))

            # 示例健康评分
            today = datetime.now().date()
            for i in range(30):
                date = today - timedelta(days=i)
                cursor.execute("INSERT IGNORE INTO health_scores (user_id, score, date) VALUES (1, %s, %s)",
                               (random.randint(40,100), date))
                cursor.execute("INSERT IGNORE INTO health_scores (user_id, score, date) VALUES (2, %s, %s)",
                               (random.randint(40,100), date))
                cursor.execute("INSERT IGNORE INTO health_scores (user_id, score, date) VALUES (3, %s, %s)",
                               (random.randint(40,100), date))
            conn.commit()
        except mysql.connector.Error:
            pass

        cursor.close()
        conn.close()


# 用户认证
def authenticate(username, password):
    conn = get_db_connection()
    if conn:
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT * FROM users WHERE username = %s", (username,))
        user = cursor.fetchone()
        cursor.close()
        conn.close()

        if user and user['password_hash'] == hashlib.sha256(password.encode()).hexdigest():
            return user
    return None


# 获取所有用户列表
def get_all_users():
    conn = get_db_connection()
    if conn:
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT id, username FROM users")
        users = cursor.fetchall()
        cursor.close()
        conn.close()
        return users
    return []


# 获取用户健康数据
def get_health_data(user_id=None):
    conn = get_db_connection()
    if conn:
        cursor = conn.cursor(dictionary=True)

        if user_id is None:  # 获取所有用户数据
            query = """
            SELECT u.username, h.score, h.date 
            FROM health_scores h
            JOIN users u ON h.user_id = u.id
            ORDER BY h.date DESC
            """
            cursor.execute(query)
        else:  # 获取特定用户数据
            query = "SELECT score, date FROM health_scores WHERE user_id = %s ORDER BY date DESC"
            cursor.execute(query, (user_id,))

        data = cursor.fetchall()
        cursor.close()
        conn.close()

        return pd.DataFrame(data)
    return pd.DataFrame()


# 获取低分用户
def get_low_score_users(threshold=60):
    conn = get_db_connection()
    if conn:
        cursor = conn.cursor(dictionary=True)
        query = """
        SELECT u.username, MAX(h.date) as last_date, h.score
        FROM health_scores h
        JOIN users u ON h.user_id = u.id
        WHERE h.date = (SELECT MAX(date) FROM health_scores WHERE user_id = u.id)
        GROUP BY u.id
        HAVING h.score < %s
        ORDER BY h.score ASC
        """
        cursor.execute(query, (threshold,))
        data = cursor.fetchall()
        cursor.close()
        conn.close()
        return pd.DataFrame(data)
    return pd.DataFrame()


# 删除健康数据
def delete_health_data(user_id, date_to_delete):
    conn = get_db_connection()
    if conn:
        try:
            cursor = conn.cursor()
            # 删除指定日期的数据
            cursor.execute("DELETE FROM health_scores WHERE user_id = %s AND date = %s",
                           (user_id, date_to_delete))
            conn.commit()
            return cursor.rowcount  # 返回删除的行数
        except mysql.connector.Error as err:
            st.error(f"删除数据时出错: {err}")
            return 0
        finally:
            cursor.close()
            conn.close()
    return 0


# 登录页面
def login_page():
    st.title("心理健康分析系统 - 登录")

    with st.form("login_form"):
        username = st.text_input("用户名")
        password = st.text_input("密码", type="password")
        submitted = st.form_submit_button("登录")

        if submitted:
            user = authenticate(username, password)
            if user:
                st.session_state["authenticated"] = True
                st.session_state["user"] = user
                st.success(f"欢迎, {user['username']}!")
                st.rerun()
            else:
                st.error("用户名或密码错误")


def main_page():
    user = st.session_state.user
    is_admin = user.get('is_admin', False)

    if is_admin:
        st.title(f"心理健康分析系统 - 管理员面板")

        # 管理员功能：查看所有用户数据
        st.sidebar.header("管理员功能")
        all_users = get_all_users()
        user_options = {user['id']: user['username'] for user in all_users}
        selected_user_id = st.sidebar.selectbox(
            "选择查看的用户",
            options=list(user_options.keys()),
            format_func=lambda uid: user_options[uid]
        )

        # 管理员功能：查看低分用户
        threshold = st.sidebar.slider("低分阈值", 0, 100, 60)
        low_score_users = get_low_score_users(threshold)

        # 显示低分用户
        st.header("低分用户预警")
        if not low_score_users.empty:
            st.write(f"以下用户的最近健康评分低于 {threshold} 分:")
            st.dataframe(low_score_users, hide_index=True)

            # 可视化低分用户
            fig_low = px.bar(
                low_score_users,
                x='username',
                y='score',
                color='score',
                title=f'健康评分低于{threshold}分的用户',
                labels={'username': '用户名', 'score': '健康评分'},
                color_continuous_scale='RdYlGn_r'
            )
            st.plotly_chart(fig_low, use_container_width=True)
        else:
            st.success(f"目前没有健康评分低于 {threshold} 分的用户")

        # 获取选定用户的数据
        df = get_health_data(selected_user_id)
        st.title(f"查看用户: {user_options[selected_user_id]}")
    else:
        st.title(f"心理健康分析系统 - 欢迎, {user['username']}")
        df = get_health_data(user['id'])

    # 日期范围选择
    if not df.empty:
        min_date = df["date"].min()
        max_date = df["date"].max()
        date_range = st.sidebar.date_input(
            "选择日期范围",
            value=(min_date, max_date),
            min_value=min_date,
            max_value=max_date
        )
    else:
        date_range = st.sidebar.date_input("选择日期范围", value=(datetime.now().date(), datetime.now().date()))
        st.warning("没有可用的健康数据")
        return

    # 指标选择
    metrics = ["score"]
    selected_metrics = metrics

    # 图表类型选择
    chart_type = st.sidebar.selectbox(
        "选择图表类型",
        ["折线图", "柱状图"]
    )

    # 应用筛选
    if len(date_range) == 2:
        filtered_df = df[(df["date"] >= date_range[0]) & (df["date"] <= date_range[1])]
    else:
        filtered_df = df

    # 主界面
    st.header("健康数据概览")

    # 显示最新健康评分
    if len(filtered_df) >= 2:
        latest_score = filtered_df.iloc[0]['score']  # 因为按日期降序排列
        prev_score = filtered_df.iloc[1]['score']
        score_color = "green" if latest_score >= 80 else "orange" if latest_score >= 60 else "red"
        st.metric(
            label="当前健康评分",
            value=f"{latest_score}/100",
            delta=f"{latest_score - prev_score} (较前次)"
        )
    elif len(filtered_df) == 1:
        latest_score = filtered_df.iloc[0]['score']
        score_color = "green" if latest_score >= 80 else "orange" if latest_score >= 60 else "red"
        st.metric(
            label="当前健康评分",
            value=f"{latest_score}/100"
        )
    else:
        st.warning("没有可用的健康数据")

    # 健康评分等级
    if len(filtered_df) > 0:
        st.progress(latest_score / 100,
                    text=f"健康等级: {'优秀' if latest_score >= 80 else '良好' if latest_score >= 60 else '需改善'}")

    # 数据表格展示
    st.header("详细数据")
    st.dataframe(
        filtered_df,
        use_container_width=True,
        hide_index=True
    )

    # 图表展示
    st.header("趋势分析")

    if len(selected_metrics) > 0 and len(filtered_df) > 0:
        if chart_type == "折线图":
            fig = px.line(
                filtered_df,
                x="date",
                y=selected_metrics,
                title="健康指标趋势",
                labels={"value": "数值", "variable": "指标"}
            )
        elif chart_type == "柱状图":
            fig = px.bar(
                filtered_df,
                x="date",
                y=selected_metrics,
                title="健康指标对比",
                barmode="group",
                labels={"value": "数值", "variable": "指标"}
            )

        st.plotly_chart(fig, use_container_width=True)

        # 相关性分析
        if len(selected_metrics) >= 2 and chart_type != "散点图":
            st.subheader("指标相关性")
            corr = filtered_df[selected_metrics].corr()
            st.dataframe(corr.style.background_gradient(cmap="coolwarm", axis=None))

            # 相关性热力图
            fig_corr = px.imshow(
                corr,
                text_auto=True,
                aspect="auto",
                color_continuous_scale="RdBu",
                title="指标相关性热力图"
            )
            st.plotly_chart(fig_corr, use_container_width=True)
    else:
        st.warning("没有足够的数据进行趋势分析")

    # 添加删除数据功能（仅限非管理员或管理员查看自己数据时）
    if not is_admin or (is_admin and selected_user_id == user['id']):
        st.sidebar.header("数据管理")

        # 获取所有日期用于选择
        all_dates = df["date"].unique()

        if len(all_dates) > 0:
            # 按日期排序（最新的在前）
            sorted_dates = sorted(all_dates, reverse=True)

            # 选择要删除的日期
            date_to_delete = st.sidebar.selectbox(
                "选择要删除的日期",
                sorted_dates,
                key="delete_date_selector"
            )

            # 显示确认对话框
            if st.sidebar.button("删除选定日期的数据", type="primary"):
                st.sidebar.warning("您确定要删除数据吗？此操作不可撤销！")

                col1, col2 = st.sidebar.columns(2)
                with col1:
                    if st.button("确认删除"):
                        # 执行删除操作
                        target_user_id = selected_user_id if is_admin else user['id']
                        deleted_count = delete_health_data(target_user_id, date_to_delete)
                        if deleted_count > 0:
                            st.success(f"成功删除 {date_to_delete} 的数据！")
                            # 刷新页面
                            st.rerun()
                        else:
                            st.error("删除失败或没有数据可删除")
                with col2:
                    if st.button("取消"):
                        pass
        else:
            st.sidebar.info("没有可删除的数据")

    # 添加登出按钮
    if st.sidebar.button("登出"):
        st.session_state["authenticated"] = False
        st.session_state.pop("user", None)
        st.rerun()


# 主应用程序
def main():
    initialize_database()  # 初始化数据库
    # 初始化会话状态
    if "authenticated" not in st.session_state:
        st.session_state["authenticated"] = False
    # 根据认证状态显示不同页面
    if st.session_state["authenticated"]:
        main_page()
    else:
        login_page()


if __name__ == "__main__":
    main()